package io.renren.modules.lucky.dao;

import io.renren.common.dao.BaseDao;
import io.renren.modules.lucky.dto.background.LuckyIndexChartsResp;
import io.renren.modules.lucky.dto.background.ReportReqDto;
import io.renren.modules.lucky.dto.background.LuckyIndexResp;
import io.renren.modules.lucky.entity.LuckyRecordEntity;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * 招财猫后台记录
 *
 * @author Liang Pei 
 * @since 1.0.0 2025-01-06
 */
@Mapper
public interface LuckyRecordDao extends BaseDao<LuckyRecordEntity> {


    @Select("SELECT plat_code, create_time, SUM(uv) as totalUv, SUM(pv) as totalPv, SUM(clicks) as totalClicks, SUM(forecast_revenue) as totalForecastRevenue " +
            "FROM lucky_record " +
            "WHERE plat_code = #{reportReq.platCode} " +
            "AND create_time BETWEEN CONCAT(#{reportReq.startTime}, ' 00:00:00') AND CONCAT(#{reportReq.endTime}, ' 23:59:59') " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')")
    List<LuckyRecordEntity> queryByDay(@Param("reportReq") ReportReqDto reportReq);

    @Select("SELECT plat_code, media_id, SUM(uv) as totalUv, SUM(pv) as totalPv, SUM(clicks) as totalClicks, SUM(forecast_revenue) as totalForecastRevenue " +
            "FROM lucky_record " +
            "WHERE plat_code = #{reportReq.platCode} " +
            "AND create_time BETWEEN CONCAT(#{reportReq.startTime}, ' 00:00:00') AND CONCAT(#{reportReq.endTime}, ' 23:59:59') " +
            "GROUP BY media_id")
    List<LuckyRecordEntity> queryByMedia(@Param("reportReq") ReportReqDto reportReq);

    @Select("SELECT plat_code, create_time, media_id, SUM(uv) as totalUv, SUM(pv) as totalPv, SUM(clicks) as totalClicks, SUM(forecast_revenue) as totalForecastRevenue " +
            "FROM lucky_record " +
            "WHERE plat_code = #{reportReq.platCode} " +
            "AND create_time BETWEEN CONCAT(#{reportReq.startTime}, ' 00:00:00') AND CONCAT(#{reportReq.endTime}, ' 23:59:59') " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d'), media_id")
    List<LuckyRecordEntity> queryByDayAndMedia(@Param("reportReq") ReportReqDto reportReq);

    @Select("SELECT plat_code, point_num, SUM(uv) as totalUv, SUM(pv) as totalPv, SUM(clicks) as totalClicks, SUM(forecast_revenue) as totalForecastRevenue " +
            "FROM lucky_record " +
            "WHERE plat_code = #{reportReq.platCode} " +
            "AND create_time BETWEEN CONCAT(#{reportReq.startTime}, ' 00:00:00') AND CONCAT(#{reportReq.endTime}, ' 23:59:59') " +
            "GROUP BY point_num")
    List<LuckyRecordEntity> queryByPointNum(@Param("reportReq") ReportReqDto reportReq);

    @Select("SELECT plat_code, create_time, point_num, SUM(uv) as totalUv, SUM(pv) as totalPv, SUM(clicks) as totalClicks, SUM(forecast_revenue) as totalForecastRevenue " +
            "FROM lucky_record " +
            "WHERE plat_code = #{reportReq.platCode} " +
            "AND create_time BETWEEN CONCAT(#{reportReq.startTime}, ' 00:00:00') AND CONCAT(#{reportReq.endTime}, ' 23:59:59') " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d'), point_num")
    List<LuckyRecordEntity> queryByDayAndPointNum(@Param("reportReq") ReportReqDto reportReq);

    @Select("SELECT plat_code, media_id, point_num, SUM(uv) as totalUv, SUM(pv) as totalPv, SUM(clicks) as totalClicks, SUM(forecast_revenue) as totalForecastRevenue " +
            "FROM lucky_record " +
            "WHERE plat_code = #{reportReq.platCode} " +
            "AND create_time BETWEEN CONCAT(#{reportReq.startTime}, ' 00:00:00') AND CONCAT(#{reportReq.endTime}, ' 23:59:59') " +
            "GROUP BY media_id, point_num")
    List<LuckyRecordEntity> queryByMediaAndPointNum(@Param("reportReq") ReportReqDto reportReq);

    @Select("SELECT plat_code, province, SUM(uv) as totalUv, SUM(pv) as totalPv, SUM(clicks) as totalClicks, SUM(forecast_revenue) as totalForecastRevenue " +
            "FROM lucky_record " +
            "WHERE plat_code = #{reportReq.platCode} " +
            "AND create_time BETWEEN CONCAT(#{reportReq.startTime}, ' 00:00:00') AND CONCAT(#{reportReq.endTime}, ' 23:59:59') " +
            "GROUP BY province")
    List<LuckyRecordEntity> queryByProvince(@Param("reportReq") ReportReqDto reportReq);

    @Select("SELECT plat_code, create_time, province, SUM(uv) as totalUv, SUM(pv) as totalPv, SUM(clicks) as totalClicks, SUM(forecast_revenue) as totalForecastRevenue " +
            "FROM lucky_record " +
            "WHERE plat_code = #{reportReq.platCode} " +
            "AND create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59') " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d'), province")
    List<LuckyRecordEntity> queryByDayAndProvince(@Param("reportReq") ReportReqDto reportReq);


    /**
     * 查询昨日的uv 点击量 预估收益
     */
    @Select("SELECT plat_code, SUM(uv) as yesterdayExposure, SUM(clicks) as yesterdayClick, SUM(forecast_revenue) as yesterdayIncome " +
            "FROM lucky_record " +
            "WHERE plat_code = #{platCode} " +
            "AND create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')")
    LuckyIndexResp queryYesterdayData(@Param("platCode") String platCode, @Param("startTime") String startTime, @Param("endTime") String endTime);


    /**
     * 查询前七日的uv 点击量 预估收益
     */
    @Select("SELECT plat_code, SUM(uv) as sevenDaysExposure, SUM(clicks) as sevenDaysClick, SUM(forecast_revenue) as sevenDaysIncome " +
            "FROM lucky_record " +
            "WHERE plat_code = #{platCode} " +
            "AND create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')")
    LuckyIndexResp querySevenDaysData(@Param("platCode") String platCode, @Param("startTime") String startTime, @Param("endTime") String endTime);

    /**
     * 查询本月的uv 点击量 预估收益
     */
    @Select("SELECT plat_code, SUM(uv) as monthExposure, SUM(clicks) as monthClick, SUM(forecast_revenue) as monthIncome " +
            "FROM lucky_record " +
            "WHERE plat_code = #{platCode} " +
            "AND create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59')")
    LuckyIndexResp queryMonthData(@Param("platCode") String platCode, @Param("startTime") String startTime, @Param("endTime") String endTime);

    /**
     * 查询指定日期的 每日uv 点击量 预估收益
     */
    @Select("SELECT plat_code, DATE_FORMAT(create_time, '%Y-%m-%d') as dateStr, SUM(uv) as exposure, SUM(clicks) as click, SUM(forecast_revenue) as income " +
            "FROM lucky_record " +
            "WHERE plat_code = #{platCode} " +
            "AND create_time BETWEEN CONCAT(#{startTime}, ' 00:00:00') AND CONCAT(#{endTime}, ' 23:59:59') " +
            "GROUP BY DATE_FORMAT(create_time, '%Y-%m-%d')")
    List<LuckyIndexChartsResp> queryByDay(@Param("platCode") String platCode, @Param("startTime") String startTime, @Param("endTime") String endTime);
}